package com.glo.spreadsheet2db.services.jdbc.repositories;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.glo.spreadsheet2db.model.SpreadsheetCell;
import com.glo.spreadsheet2db.services.repositories.SpreadsheetCellRepository;

@Repository
public class JdbcTemplateSpreadsheetCellRepository implements
		SpreadsheetCellRepository, InitializingBean {

	@Value("${jdbc.sql.spreadsheetCell.queryById}")
	private String spreadsheetCellByIdQuery;

	@Value("${jdbc.sql.spreadsheetCell.queryBySheetId}")
	private String spreadsheetCellBySheetIdQuery;

	@Value("${jdbc.sql.spreadsheetCell.insert}")
	private String insertSpreadsheetCellQuery;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public SpreadsheetCell getSpreadsheetCellById(long id) {
		return jdbcTemplate.queryForObject(spreadsheetCellByIdQuery,
				spreadsheetCellMapper, id);
	}

	public List<SpreadsheetCell> getSpreadsheetCellBySheetId(long sheetId) {
		return jdbcTemplate.query(spreadsheetCellBySheetIdQuery,
				spreadsheetCellMapper, sheetId);
	}

	public SpreadsheetCell saveSpreadsheetCell(SpreadsheetCell spreadsheetCell) {

		SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.setTableName("spreadsheetCell");
		simpleJdbcInsert.setColumnNames(Arrays.asList("value", "colIndex",
				"rowIndex", "sheetId"));
		simpleJdbcInsert.setGeneratedKeyName("id");

		Map<String, Object> args = new HashMap<String, Object>();
		args.put("value", spreadsheetCell.getValue());
		args.put("colIndex", spreadsheetCell.getColumnIndex());
		args.put("rowIndex", spreadsheetCell.getRowIndex());
		args.put("sheetId", spreadsheetCell.getSheetId());

		Number id = simpleJdbcInsert.executeAndReturnKey(args);
		spreadsheetCell.setId(id.longValue());
		return spreadsheetCell;
	}

	public void afterPropertiesSet() throws Exception {
		Assert.notNull(this.jdbcTemplate, "the jdbcTemplate can't be null!");
		Assert.notNull(this.spreadsheetCellByIdQuery,
				"the spreadsheetCellByIdQuery can't be null");
		Assert.notNull(this.spreadsheetCellBySheetIdQuery,
				"the spreadsheetCellBySheetIdQuery can't be null");
		Assert.notNull(this.insertSpreadsheetCellQuery,
				"the insertSpreadsheetCellQuery can't be null");
	}

	private RowMapper<SpreadsheetCell> spreadsheetCellMapper = new RowMapper<SpreadsheetCell>() {

		public SpreadsheetCell mapRow(ResultSet resultSet, int i)
				throws SQLException {
			long id = resultSet.getLong("id");
			String value = resultSet.getString("value");
			int columnIndex = resultSet.getInt("colIndex");
			int rowIndex = resultSet.getInt("rowIndex");
			long sheetId = resultSet.getLong("sheetId");
			return new SpreadsheetCell(id, value, columnIndex, rowIndex, sheetId);
		}
	};
}
